/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package core;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import javax.swing.JFrame;
import javax.swing.JTable;
import util.JTableUtil;

/**
 *
 * @author Maniek
 */
public class WynikiBadanProstych {
    private JFrame parent;
    
    public WynikiBadanProstych(JFrame parent){
        this.parent=parent;
    }
    
    public JTable getBadaniaMorfologia(int idpa) throws Exception{
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT S.id AS Id, S.data_start AS Data FROM badania_proste_terminy S JOIN wyniki_morfologia U ON S.id = U.id_terminu WHERE s.id_pacjenta='"+idpa+"' ORDER BY S.data_start DESC");
            ResultSet rs = stmn.getResultSet();  
            return JTableUtil.createTable(rs);
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return null;
    }
    
    public JTable getBadaniaMocz(int idpa) throws Exception{
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT S.id AS Id, S.data_start AS Data FROM badania_proste_terminy S JOIN wyniki_badan_moczu U ON S.id = U.id_terminu WHERE s.id_pacjenta='"+idpa+"' ORDER BY S.data_start DESC");
            ResultSet rs = stmn.getResultSet();  
            return JTableUtil.createTable(rs);
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return null;
    }
    
    public JTable getBadaniaLipidogram(int idpa) throws Exception{
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT S.id AS Id, S.data_start AS Data FROM badania_proste_terminy S JOIN wyniki_lipidogram U ON S.id = U.id_terminu WHERE s.id_pacjenta='"+idpa+"' ORDER BY S.data_start DESC");
            ResultSet rs = stmn.getResultSet();  
            return JTableUtil.createTable(rs);
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return null;
    }
    
    public static ResultSet getWynikiMocz(int idpa) throws Exception{
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT obj, ciezar_wlasciwy, ph, bialko, glukoza, ciala_ketonowe, bilirubina, urobilinogen, azotyny, erytrocyty, leukocyty, nablonki, waleczki, krysztaly  FROM wyniki_badan_moczu WHERE id_terminu = '"+idpa+"'");
            ResultSet rs = stmn.getResultSet();
            return rs;
           // return rs.getString(1);         
        } catch (SQLException ex ) {
            System.out.println("Pacjenci.getWynikiMocz - blad z baza danych");
          // ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return null;
    }
    
    public static ResultSet getWynikiLipidogram(int idpa) throws Exception{
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT cholesterol_calkowity, tg, ldl, hdl, lipoptoteina_a FROM wyniki_lipidogram WHERE id_terminu = '"+idpa+"'");
            ResultSet rs = stmn.getResultSet();
            return rs;
           // return rs.getString(1);         
        } catch (SQLException ex ) {
            System.out.println("Pacjenci.getWynikiLipidogram - blad z baza danych");
          // ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return null;
    }
    
    public static ResultSet getWynikiMorfologia(int idpa) throws Exception{
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT rbc, retikulocyty, mcv, mch, mchc, hemoglobina, hematokryt, wbc, granulocyty, neut, eos, baso, lymph, limfocyty_b, limfocyty_t, nk, mono, plt FROM wyniki_morfologia WHERE id_terminu = '"+idpa+"'");
            ResultSet rs = stmn.getResultSet();
            return rs;
           // return rs.getString(1);         
        } catch (SQLException ex ) {
            System.out.println("Pacjenci.getWynikiMorfologia - blad z baza danych");
          // ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return null;
    }
    
    public ResultSet getBadania(int id, Date from,Date to)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "SELECT id_pacjenta AS id_pacjenta , id_medyka AS id_medyka , id_gabinetu AS id_gabinetu, "
                    + " data_start AS data_start , data_stop AS data_stop FROM badania_proste_terminy"
                    + " WHERE  id_badania_prostego = ? AND data_start>=? AND data_stop<=?",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, id);
            stmt.setTimestamp(2, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(3, new java.sql.Timestamp(to.getTime()));
            return stmt.executeQuery();
        }
        catch(SQLException ex)
        {
       //     ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
        return null;
    }
    
    public static void addBadania_WolneTerminy(Date from,Date to, User person, int id_badania)
    {
        if(from.compareTo(to) > 0) {
           Date temp = to;
           to = from;
           from = temp;
        } 
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "INSERT INTO badania_proste_terminy VALUES(?,?,?,?,?,?)",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, person.getID());
            stmt.setInt(2, id_badania);
            stmt.setInt(3, 20);
            stmt.setInt(4, Users.getDoctorByPacient_ID(person.getID()));
            stmt.setTimestamp(5, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(6, new java.sql.Timestamp(to.getTime()));
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
        //    ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
    public static void delBadania_WolneTerminy(Date from,Date to, User person, int id_badania)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "DELETE FROM badania_proste_terminy WHERE id_pacjenta='"+person.getID()+"' AND id_badania='"+id_badania+"' AND data_start='"+new java.sql.Timestamp(from.getTime())+"'",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
      //      ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
        public ResultSet getBadaniaRTG(Date from,Date to)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "SELECT id_pacjenta AS id_pacjenta , id_medyka AS id_medyka , id_gabinetu AS id_gabinetu, "
                    + " data_start AS data_start , data_stop AS data_stop FROM terminarz_rtg"
                    + " WHERE  data_start>=? AND data_stop<=?",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setTimestamp(1, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(2, new java.sql.Timestamp(to.getTime()));
            return stmt.executeQuery();
        }
        catch(SQLException ex)
        {
       //     ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
        return null;
    }
        
            public static void addBadanieRTG(Date from,Date to, User person)
    {
        if(from.compareTo(to) > 0) {
           Date temp = to;
           to = from;
           from = temp;
        } 
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "INSERT INTO terminarz_rtg VALUES(?,?,?,?,?)",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, person.getID());
            stmt.setInt(2, Users.getDoctorByPacient_ID(person.getID()));
            stmt.setInt(3, 50);
            stmt.setTimestamp(4, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(5, new java.sql.Timestamp(to.getTime()));
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
        //    ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
    public static void delBadanieRTG(Date from,Date to, User person)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "DELETE FROM terminarz_rtg WHERE id_pacjenta='"+person.getID()+"' AND data_start='"+new java.sql.Timestamp(from.getTime())+"'",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
      //      ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
            public ResultSet getBadaniaUSG(Date from,Date to)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "SELECT id_pacjenta AS id_pacjenta , id_medyka AS id_medyka , id_gabinetu AS id_gabinetu, "
                    + " data_start AS data_start , data_stop AS data_stop FROM terminarz_usg"
                    + " WHERE  data_start>=? AND data_stop<=?",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setTimestamp(1, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(2, new java.sql.Timestamp(to.getTime()));
            return stmt.executeQuery();
        }
        catch(SQLException ex)
        {
       //     ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
        return null;
    }
        
            public static void addBadanieUSG(Date from,Date to, User person)
    {
        if(from.compareTo(to) > 0) {
           Date temp = to;
           to = from;
           from = temp;
        } 
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "INSERT INTO terminarz_usg VALUES(?,?,?,?,?)",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, person.getID());
            stmt.setInt(2, Users.getDoctorByPacient_ID(person.getID()));
            stmt.setInt(3, 18);
            stmt.setTimestamp(4, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(5, new java.sql.Timestamp(to.getTime()));
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
        //    ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
    public static void delBadanieUSG(Date from,Date to, User person)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "DELETE FROM terminarz_usg WHERE id_pacjenta='"+person.getID()+"' AND data_start='"+new java.sql.Timestamp(from.getTime())+"'",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
      //      ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
    public ResultSet getSzczepionki(Date from,Date to)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "SELECT id_pacjenta AS id_pacjenta , id_medyka AS id_medyka , id_gabinetu AS id_gabinetu, "
                    + " data_start AS data_start , data_stop AS data_stop FROM terminarz_szczepienia"
                    + " WHERE  data_start>=? AND data_stop<=?",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setTimestamp(1, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(2, new java.sql.Timestamp(to.getTime()));
            return stmt.executeQuery();
        }
        catch(SQLException ex)
        {
            System.out.println("Szczepionki get problem");
       //     ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
        return null;
    }
        
    public static void addSzczepionki(Date from,Date to, User person, int szczepionka)
    {
        if(from.compareTo(to) > 0) {
           Date temp = to;
           to = from;
           from = temp;
        } 
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "INSERT INTO terminarz_szczepienia VALUES(?,?,?,?,?,?)",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, person.getID());
            stmt.setInt(2, Users.getDoctorByPacient_ID(person.getID()));
            stmt.setInt(3, 18);
            stmt.setInt(4, szczepionka);
            stmt.setTimestamp(5, new java.sql.Timestamp(from.getTime()));
            stmt.setTimestamp(6, new java.sql.Timestamp(to.getTime()));
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
                        System.out.println("Szczepionki add problem");
        //    ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
    public static void delSzczepionki(Date from,Date to, User person)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "DELETE FROM terminarz_szczepienia WHERE id_pacjenta='"+person.getID()+"' AND data_start='"+new java.sql.Timestamp(from.getTime())+"'",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
      //      ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
    
        public JTable getBadaniaMocz() throws Exception{
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT S.id AS Id, S.data_start AS Data,  W.imie+' '+W.nazwisko AS Pacjent FROM badania_proste_terminy S JOIN users W ON W.id = S.id_pacjenta WHERE S.id_badania_prostego=1 AND S.data_start < GETUTCDATE() AND NOT EXISTS (SELECT * FROM wyniki_badan_moczu K WHERE K.id_terminu = S.id) ORDER BY S.data_start DESC");
            ResultSet rs = stmn.getResultSet();  
            return JTableUtil.createTable(rs);
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return null;
    }

        public JTable getLipidogram() throws Exception{
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT S.id AS Id, S.data_start AS Data,  W.imie+' '+W.nazwisko AS Pacjent FROM badania_proste_terminy S JOIN users W ON W.id = S.id_pacjenta WHERE S.id_badania_prostego=2 AND S.data_start < GETUTCDATE() AND NOT EXISTS (SELECT * FROM wyniki_lipidogram K WHERE K.id_terminu = S.id) ORDER BY S.data_start DESC");
            ResultSet rs = stmn.getResultSet();  
            return JTableUtil.createTable(rs);
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return null;
    }
       
         public JTable getMorfologia() throws Exception{
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT S.id AS Id, S.data_start AS Data,  W.imie+' '+W.nazwisko AS Pacjent FROM badania_proste_terminy S JOIN users W ON W.id = S.id_pacjenta WHERE S.id_badania_prostego=3 AND S.data_start < GETUTCDATE() AND NOT EXISTS (SELECT * FROM wyniki_morfologia K WHERE K.id_terminu = S.id) ORDER BY S.data_start DESC");
            ResultSet rs = stmn.getResultSet();  
            return JTableUtil.createTable(rs);
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return null;
    }
         
     public static void addBadanieMocz(int id_terminu, String o1, String o2,String o3,String o4,String o5,String o6,String o7,String o8,String o9,String o10,String o11,String o12,String o13,String o14)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "INSERT INTO wyniki_badan_moczu VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, id_terminu);
            stmt.setFloat(2, Float.parseFloat(o1));
            stmt.setFloat(3, Float.parseFloat(o2));
            stmt.setFloat(4, Float.parseFloat(o3));
            stmt.setFloat(5, Float.parseFloat(o4));
            stmt.setFloat(6, Float.parseFloat(o5));
            stmt.setFloat(7, Float.parseFloat(o6));
            stmt.setFloat(8, Float.parseFloat(o7));
            stmt.setFloat(9, Float.parseFloat(o8));
            stmt.setFloat(10, Float.parseFloat(o9));
            stmt.setFloat(11, Float.parseFloat(o10));
            stmt.setFloat(12, Float.parseFloat(o11));
            stmt.setFloat(13, Float.parseFloat(o12));
            stmt.setFloat(14, Float.parseFloat(o13));
            stmt.setFloat(15, Float.parseFloat(o14));
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
        //    ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
        
      public static void addLipidogram(int id_terminu, String o1, String o2,String o3,String o4,String o5)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "INSERT INTO wyniki_lipidogram VALUES(?,?,?,?,?,?)",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, id_terminu);
            stmt.setFloat(2, Float.parseFloat(o1));
            stmt.setFloat(3, Float.parseFloat(o2));
            stmt.setFloat(4, Float.parseFloat(o3));
            stmt.setFloat(5, Float.parseFloat(o4));
            stmt.setFloat(6, Float.parseFloat(o5));
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
        //    ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }      
 
           public static void addBadanieMorfologia(int id_terminu, String o1, String o2,String o3,String o4,String o5,String o6,String o7,String o8,String o9,String o10,String o11,String o12,String o13,String o14,String o15,String o16,String o17,String o18)
    {
        try{ 
            PreparedStatement stmt = SqlConnection.getInstance().getSqlConnection().prepareStatement(
                    "INSERT INTO wyniki_morfologia VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1, id_terminu);
            stmt.setFloat(2, Float.parseFloat(o1));
            stmt.setFloat(3, Float.parseFloat(o2));
            stmt.setFloat(4, Float.parseFloat(o3));
            stmt.setFloat(5, Float.parseFloat(o4));
            stmt.setFloat(6, Float.parseFloat(o5));
            stmt.setFloat(7, Float.parseFloat(o6));
            stmt.setFloat(8, Float.parseFloat(o7));
            stmt.setFloat(9, Float.parseFloat(o8));
            stmt.setFloat(10, Float.parseFloat(o9));
            stmt.setFloat(11, Float.parseFloat(o10));
            stmt.setFloat(12, Float.parseFloat(o11));
            stmt.setFloat(13, Float.parseFloat(o12));
            stmt.setFloat(14, Float.parseFloat(o13));
            stmt.setFloat(15, Float.parseFloat(o14));
            stmt.setFloat(16, Float.parseFloat(o15));
            stmt.setFloat(17, Float.parseFloat(o16));
            stmt.setFloat(18, Float.parseFloat(o17));
            stmt.setFloat(19, Float.parseFloat(o18));
            stmt.executeUpdate();
        }
         catch(SQLException ex)
        {
        //    ErrorLogger.getInstance().log(ex.getMessage(), true, user.getJFrameParent());
        }
    }
      
}
